package database;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public abstract class Table {
	
	protected final String INSERT_STMT;
	protected final String DELETE_STMT;
	protected final String DISPLAY_QUERY;
	protected final String EXISTS_QUERY;
	
	protected DBConnection dbCon = null;
	
	/**
	 * Table constructor
	 * 
	 * @param insertStmt pre-canned insert String for this instantiation of the Table class
	 * @param deleteStmt pre-canned delete String for this instantiation of the Table class
	 * @param displayStmt pre-canned 
	 */
	protected Table(String insertStmt, String deleteStmt, String displayQuery, String  existsQuery)
	{
		INSERT_STMT = 	insertStmt;
		DELETE_STMT = 	deleteStmt;
		DISPLAY_QUERY = displayQuery;		
		EXISTS_QUERY = 	existsQuery;
		
		dbCon = DBConnection.getInstance();
	}
	
	/**
	 * get column names returns the column names of the table
	 * 
	 * @return column names as a list of strings
	 */
	abstract public List<String> getColumnNames();	
	
	/**
	 * get primary key column names returns the names of primary key columns of the table
	 * 
	 * @return primary key column names as a list of strings
	 */
	abstract public List<String> getPrimaryKeyColumnNames();
	
	/**
	 * get primary key values returns the values in a list of values to be inserted that correspond
	 * to primary key columns
	 * 
	 * @param insertValues the values being inserted into the DB
	 * @return the values that are primary keys as a list of strings
	 */
	public abstract List<String> getPrimaryKeyValues(List<String> insertValues);
	
	/**
	 * insert method for inserting a list of values into a table 
	 * @param insertValues - values to be inserted
	 * @return if the insert was successful
	 */
	public void insert(List<String> insertValues) throws SQLException
	{
		List<String> primaryKeyValues = getPrimaryKeyValues(insertValues);
		String fmtdExistsQuery = String.format(EXISTS_QUERY, primaryKeyValues.toArray());
		ResultSet rs = dbCon.executeQuery(fmtdExistsQuery);
		
		if(rs != null)
		{
			if (!rs.next())
			{
				String fmtdInsertStmt = String.format(INSERT_STMT, insertValues.toArray());
				dbCon.executeStatement(fmtdInsertStmt);
			}
			
			rs.close();
		}	
	}
	
	/**
	 * delete method for deleting a row from  a table
	 * @param keyValues - the values of the key entries to use for finding the row to delete
	 * @return if the delete was successful
	 */
	public void delete(List<String> keyValues) throws SQLException
	{
		String fmtdDeleteStmt = String.format(DELETE_STMT, keyValues.toArray());
		dbCon.executeStatement(fmtdDeleteStmt);		 
	}
	
	/**
	 * method for displaying a table
	 * @return the list of rows in the table (with each row represented as a lists of Strings)
	 */
	public List<List<String>> display() throws SQLException
	{
		List<List<String>> displayLists = new ArrayList<List<String>>();
			
		ResultSet rs = dbCon.executeQuery(DISPLAY_QUERY);
		if(rs != null)
		{
			ResultSetMetaData rsmd = rs.getMetaData();
			
				
			// add the column names as the first row 
			int colCount = rsmd.getColumnCount();
			List<String> columnNames = new ArrayList<String>(colCount);
			for(int i = 0; i < colCount; ++i)
			{
				// columns are index 1,2,...,n
				columnNames.add(rsmd.getColumnName(i+1));
			}
			displayLists.add(columnNames);
			
			// add each tuple of actual data as remaining rows
			while(rs.next())
			{
				List<String> curRow = new ArrayList<String>(colCount);
				for(int i = 0; i < colCount; ++i)
				{
					// columns are index 1,2,...,n
					curRow.add(rs.getString(i+1));
				}
				displayLists.add(curRow);
			}	
			
			rs.close();
		}
		
		return displayLists;
	}	
 }